# -*- coding:utf-8 -*-
import calendar

from . import project
from flask import render_template
from project_zczh.tool.common import login_check
from pyecharts import Line
import MySQLdb
import datetime
from decimal import Decimal


display={
    "lw":'螺纹钢',
    "gx":'高线',
    "pl":'盘螺'
}
lw_caizhi={
    'hrb400':'HRB400',
    'hrb400e':'HRB400E'
}
gx_caizhi={
    'hpb300':'HPB300'
}
pl_caizhi={
    'hrb400':'HRB400',
    'hrb400e':'HRB400E'
}
get_data_dict={
    'lw':lw_caizhi,
    'gx':gx_caizhi,
    'pl':pl_caizhi
}

def now_time(day):
    now = datetime.datetime.now().strftime("%Y-%m-%d")
    delta = datetime.timedelta(day)
    delta_now = (datetime.datetime.now() - delta).strftime("%Y-%m-%d")
    return now,delta_now


def get_data(name):
    now, delta_now = now_time(30)
    sql = """SELECT name_1 as '类型',formatr_1 as '型号',round(AVG(price),2) as '平均价格',DATE_FORMAT(time_1,%s) as a,area
                   FROM gangcai_
                   WHERE time_1<=%s AND time_1>=%s and name_1=%s and formatr_1=%s and material=%s
                   GROUP BY area,formatr_1,DATE_FORMAT(time_1,%s);"""
    cur.execute(sql, ["%Y-%m-%d", now, delta_now, name, key,material,"%Y-%m-%d"])
    data = cur.fetchall()
    updata_time=list()
    data_time = ['大区名称', '品名', '型号']
    for i in data:
        updata_time.append(str(i[3]))
    updata_time=list(set(updata_time))
    updata_time.sort()
    data_time+=updata_time
    prices=list()
    area = ['东北', '华北', '华南', '华东', '西北', '西南', '华中']
    area_list=[[addr,name,key] for addr in area]
    for k in data:
        for i in area_list:
            if k[4] == i[0]:
                i.append(int(k[2]))
    check_data=list()
    for i in area_list:
        check_data.append(len(i))
    for i in area_list:
        if len(i)==max(check_data):
            prices.append(i)
    return data_time,prices

def bar_(name,table_title, rows_data):

    attr = table_title[3:]
    data_price =[]
    for i in rows_data:
        data_price .append(i[3:])

    import numpy as np
    ndarray=np.array(data_price)
    a=np.mean(ndarray,axis=0)
    data_price=list(a)
    b=list()
    for i in data_price:
        b.append(int(i))
    data_price=b
    bar = Line("全国-%s"%name)
    bar.width=1100
    bar.add("30日走势-%s"%key,
            attr,
            data_price,
            xaxis_interval=0,
            xaxis_rotate=30,
            yaxis_rotate=30,
            is_smooth=True,
            is_label_show=True,
            mark_point=['max'])
    return bar

def table_data(name):
    now, delta_now = now_time(7)
    sql = """SELECT name_1 as '类型',formatr_1 as '型号',round(AVG(price),2) as '平均价格',DATE_FORMAT(time_1,%s) as a,area
                   FROM gangcai_
                   WHERE time_1<=%s AND time_1>=%s and name_1=%s and formatr_1=%s and material=%s
                   GROUP BY area,formatr_1,DATE_FORMAT(time_1,%s);"""
    cur.execute(sql, ["%Y-%m-%d", now, delta_now, name, key,material,"%Y-%m-%d"])
    data = cur.fetchall()
    updata_time=list()
    data_time = ['大区名称', '品名', '型号']
    for i in data:
        updata_time.append(str(i[3]))
    updata_time=list(set(updata_time))
    updata_time.sort()
    data_time+=updata_time
    prices=list()
    area = ['东北', '华北', '华南', '华东', '西北', '西南', '华中']
    area_list=[[addr,name,key] for addr in area]
    for k in data:
        for i in area_list:
            if k[4] == i[0]:
                i.append(float(Decimal(k[2]).quantize(Decimal('0.00'))))
    check_data=list()
    for i in area_list:
        check_data.append(len(i))
    for i in area_list:
        if len(i)==max(check_data):
            prices.append(i)
    #数据跟　新显示小红点　小红点
    return data_time,prices

@project.route('/table/<string:mingcheng>/<string:xinhao>/<string:caizhi>')
@login_check
def table(mingcheng,xinhao,caizhi):
    global key
    for i in display:
        if i == mingcheng:
            global name
            name=display[mingcheng]

    global material
    for i in get_data_dict:
        if mingcheng == i:
            material=get_data_dict[mingcheng][caizhi]
    key='Ф'+xinhao
    global db
    db = MySQLdb.connect(
        port=3306,
        host='127.0.0.1',
        db='zczh_data',
        user='root',
        passwd='mysql',
        charset='utf8'
    )
    global cur
    cur = db.cursor()
    now_time_point = datetime.datetime.now()
    year = now_time_point.year
    month = now_time_point.month
    date = now_time_point.day
    data_tuple = calendar.monthrange(year, month)

    if date == data_tuple[1]:
        bar = bar_month(name)
        table_title, rows_data = table_data_month(name)
    else:
        table_title, rows_data = table_data(name)
        title,rows=get_data(name)
        bar = bar_(name,title, rows)
    db.close()
    return render_template('data_gangcai.html',
                           myechart=bar.render_embed(),
                           data_time=table_title,
                           prices=rows_data,
                           key=key,
                           name=name,
                           material=material)

def bar_month(name):
    data_prict_2017, data_prict_2018=get_data_month(name)
    cur_year = datetime.datetime.now().year
    befor_year = cur_year - 1
    attr = ['{}月份'.format(i) for i in range(1,13)]
    bar = Line("全国-%s-%s" % (name,key))
    bar.width = 1100
    bar.add("%s年月走势" % cur_year,
            attr,
            data_prict_2018,
            # mark_line=["average"],
            xaxis_interval=0,
            xaxis_rotate=30,
            yaxis_rotate=30,
            is_smooth=False,
            is_label_show=True,
            # mark_point=['max']
            )

    bar.add("%s年月走势" % befor_year,
            attr,
            data_prict_2017,
            # mark_line=["average"],
            xaxis_interval=0,
            xaxis_rotate=30,
            yaxis_rotate=30,
            is_smooth=False,
            is_label_show=False,
            # mark_point=['max']
    )
    return bar

def get_data_month(name):
    cur_year = datetime.datetime.now().year
    befor_year = cur_year - 1
    befor_year_1 = str(befor_year) + '-12-31'
    befor_year_2 = str(befor_year) + '-01-01'
    cur_year_1 = str(cur_year) + '-12-31'
    cur_year_2 = str(cur_year) + '-01-01'
    sql = """SELECT name_1 as '类型',formatr_1 as '型号',round(AVG(price),2) as '平均价格',DATE_FORMAT(time_1,%s) as a
              FROM gangcai_
              WHERE time_1<=%s AND time_1>=%s and name_1=%s and formatr_1=%s and material =%s
              GROUP BY formatr_1,DATE_FORMAT(time_1,%s);"""
    cur.execute(sql, ["%Y-%m", befor_year_1, befor_year_2, name, key,material,"%Y-%m"])
    data_2017 = cur.fetchall()
    data_prict_2017 = []
    for i in data_2017:
        data_prict_2017.append(float(Decimal(i[2]).quantize(Decimal('0.00'))))
    sql = """SELECT name_1 as '类型',formatr_1 as '型号',round(AVG(price),2) as '平均价格',DATE_FORMAT(time_1,%s) as a
                 FROM gangcai_
                 WHERE time_1<=%s AND time_1>=%s and name_1=%s and formatr_1=%s and material=%s
                 GROUP BY formatr_1,DATE_FORMAT(time_1,%s);"""
    cur.execute(sql, ["%Y-%m", cur_year_1, cur_year_2, name,key,material,"%Y-%m"])

    data_2018 = cur.fetchall()
    data_prict_2018 = []
    for i in data_2018:
        data_prict_2018.append(float(Decimal(i[2]).quantize(Decimal('0.00'))))
        global data_prict_2017
        global data_prict_2018
    return data_prict_2017, data_prict_2018

def table_data_month(name):
    data_time=['{}月份'.format(i) for i in range(1,13)]
    table_title=['说明','名称','型号']+data_time
    rows_data=[]
    if len(data_prict_2017)!=len(data_prict_2018):
        a=len(data_prict_2017)-len(data_prict_2018)
        for i in range(a):
            data_prict_2018.append('-')
    cur_year = datetime.datetime.now().year
    befor_year = cur_year - 1
    rows_data.append([str(befor_year)+'年平均值',name,key]+data_prict_2017)
    rows_data.append([str(cur_year)+'年平均值',name,key]+data_prict_2018)
    return table_title,rows_data

